package com.example.demo.service.impl;

import com.example.demo.dto.DataSourceConfigDTO;
import com.example.demo.dto.QueryResultDTO;
import com.example.demo.entity.DataSourceConfig;
import com.example.demo.exception.BusinessException;
import com.example.demo.mapper.DataSourceConfigMapper;
import com.example.demo.request.CreateDataSourceConfigRequest;
import com.example.demo.request.ExecuteQueryRequest;
import com.example.demo.request.ExecuteQueryWithParamsRequest;
import com.example.demo.request.TestConnectionRequest;
import com.example.demo.service.DataSourceConfigService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class DataSourceConfigServiceImpl implements DataSourceConfigService {

    //private static final logger log = loggerFactory.getlogger(DataSourceConfigServiceImpl.class);

    @Autowired
    private DataSourceConfigMapper dataSourceConfigMapper;

    @Override
    public List<DataSourceConfigDTO> getDataSourceConfigs(int page, int size) {
        int offset = page * size;
        List<DataSourceConfig> configs = dataSourceConfigMapper.selectByPage(offset, size);
        return configs.stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public long countDataSourceConfigs() {
        return dataSourceConfigMapper.count();
    }

    @Override
    public DataSourceConfigDTO getDataSourceConfigById(Long id) {
        DataSourceConfig config = dataSourceConfigMapper.selectById(id);
        return convertToDTO(config);
    }

    @Override
    public DataSourceConfigDTO getDataSourceConfigByName(String name) {
        DataSourceConfig config = dataSourceConfigMapper.selectByName(name);
        return convertToDTO(config);
    }

    @Override
    public DataSourceConfig createDataSourceConfig(CreateDataSourceConfigRequest request) {
        // 检查数据源配置名称是否已存在
        DataSourceConfig existingConfig = dataSourceConfigMapper.selectByName(request.getName());
        if (existingConfig != null) {
            throw new BusinessException("数据源配置名称已存在：" + request.getName());
        }
        
        DataSourceConfig config = new DataSourceConfig();
        config.setName(request.getName());
        config.setDbType(request.getDbType());
        config.setHost(request.getHost());
        config.setPort(request.getPort());
        config.setUsername(request.getUsername());
        // 这里应该对密码进行加密处理
        config.setPasswordEncrypted(encryptPassword(request.getPassword()));
        config.setDatabaseName(request.getDatabaseName());
        config.setQuerySql(request.getQuerySql());
        config.setIdFields(request.getIdFields());
        config.setDisplayFields(request.getDisplayFields());
        config.setExtraFields(request.getExtraFields());
        config.setStatus(request.getStatus());
        config.setCreatedAt(LocalDateTime.now());
        config.setUpdatedAt(LocalDateTime.now());
        
        dataSourceConfigMapper.insert(config);
        return config;
    }

    @Override
    public void updateDataSourceConfig(Long id, CreateDataSourceConfigRequest request) {
        DataSourceConfig config = dataSourceConfigMapper.selectById(id);
        if (config != null) {
            // 检查数据源配置名称是否已存在（排除当前记录）
            if (!config.getName().equals(request.getName())) {
                DataSourceConfig existingConfig = dataSourceConfigMapper.selectByName(request.getName());
                if (existingConfig != null && !existingConfig.getId().equals(id)) {
                    throw new BusinessException("数据源配置名称已存在：" + request.getName());
                }
            }
            
            config.setName(request.getName());
            config.setDbType(request.getDbType());
            config.setHost(request.getHost());
            config.setPort(request.getPort());
            config.setUsername(request.getUsername());
            if (request.getPassword() != null && !request.getPassword().isEmpty()) {
                config.setPasswordEncrypted(encryptPassword(request.getPassword()));
            }
            config.setDatabaseName(request.getDatabaseName());
            config.setQuerySql(request.getQuerySql());
            config.setIdFields(request.getIdFields());
            config.setDisplayFields(request.getDisplayFields());
            config.setExtraFields(request.getExtraFields());
            config.setStatus(request.getStatus());
            config.setUpdatedAt(LocalDateTime.now());
            
            dataSourceConfigMapper.updateById(config);
        }
    }

    @Override
    public void deleteDataSourceConfig(Long id) {
        dataSourceConfigMapper.deleteById(id);
    }

    @Override
    public List<DataSourceConfigDTO> getDataSourceConfigsByStatus(Integer status) {
        List<DataSourceConfig> configs = dataSourceConfigMapper.selectByStatus(status);
        return configs.stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public List<DataSourceConfigDTO> getDataSourceConfigsByDbType(String dbType) {
        List<DataSourceConfig> configs = dataSourceConfigMapper.selectByDbType(dbType);
        return configs.stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public List<DataSourceConfigDTO> getDataSourceConfigsByStatus(Integer status, int page, int size) {
        int offset = page * size;
        List<DataSourceConfig> configs = dataSourceConfigMapper.selectByStatusWithPage(status, offset, size);
        return configs.stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public long countDataSourceConfigsByStatus(Integer status) {
        return dataSourceConfigMapper.countByStatus(status);
    }

    @Override
    public List<DataSourceConfigDTO> getDataSourceConfigsByDbType(String dbType, int page, int size) {
        int offset = page * size;
        List<DataSourceConfig> configs = dataSourceConfigMapper.selectByDbTypeWithPage(dbType, offset, size);
        return configs.stream()
                .map(this::convertToDTO)
                .collect(Collectors.toList());
    }

    @Override
    public long countDataSourceConfigsByDbType(String dbType) {
        return dataSourceConfigMapper.countByDbType(dbType);
    }

    @Override
    public boolean testConnection(Long id) {
        DataSourceConfig config = dataSourceConfigMapper.selectById(id);
        if (config == null) {
            //log.warn("数据源配置不存在，ID: {}", id);
            return false;
        }
        
        String jdbcUrl = buildJdbcUrl(config);
        if (jdbcUrl == null) {
            //log.error("不支持的数据库类型: {}", config.getDbType());
            return false;
        }
        
        // 解密密码（这里假设密码是明文存储，实际项目中需要解密）
        String password = decryptPassword(config.getPasswordEncrypted());
        
        try (Connection connection = DriverManager.getConnection(jdbcUrl, config.getUsername(), password)) {
            // 测试连接是否有效
            boolean isValid = connection.isValid(5); // 5秒超时
            if (isValid) {
                //log.info("数据库连接测试成功，数据源: {}, 类型: {}", config.getName(), config.getDbType());
            } else {
                //log.warn("数据库连接无效，数据源: {}", config.getName());
            }
            return isValid;
        } catch (SQLException e) {
            //log.error("数据库连接测试失败，数据源: {}, 错误: {}", config.getName(), e.getMessage());
            return false;
        }
    }

    @Override
    public boolean testConnectionWithParams(TestConnectionRequest request) {
        // 创建临时的DataSourceConfig对象用于测试
        DataSourceConfig tempConfig = new DataSourceConfig();
        tempConfig.setDbType(request.getDbType());
        tempConfig.setHost(request.getHost());
        tempConfig.setPort(request.getPort());
        tempConfig.setUsername(request.getUsername());
        tempConfig.setDatabaseName(request.getDatabaseName());
        
        String jdbcUrl = buildJdbcUrl(tempConfig);
        if (jdbcUrl == null) {
            //log.error("不支持的数据库类型: {}", request.getDbType());
            return false;
        }
        
        try (Connection connection = DriverManager.getConnection(jdbcUrl, request.getUsername(), request.getPassword())) {
            // 测试连接是否有效
            boolean isValid = connection.isValid(5); // 5秒超时
            if (isValid) {
                //log.info("数据库连接测试成功，类型: {}, 主机: {}:{}", request.getDbType(), request.getHost(), request.getPort());
            } else {
                //log.warn("数据库连接无效，类型: {}, 主机: {}:{}", request.getDbType(), request.getHost(), request.getPort());
            }
            return isValid;
        } catch (SQLException e) {
            //log.error("数据库连接测试失败，类型: {}, 主机: {}:{}, 错误: {}", request.getDbType(), request.getHost(), request.getPort(), e.getMessage());
            return false;
        }
    }

    @Override
    public void updateStatus(Long id, Integer status) {
        DataSourceConfig config = dataSourceConfigMapper.selectById(id);
        if (config != null) {
            config.setStatus(status);
            config.setUpdatedAt(LocalDateTime.now());
            dataSourceConfigMapper.updateById(config);
        }
    }

    private DataSourceConfigDTO convertToDTO(DataSourceConfig config) {
        if (config == null) {
            return null;
        }
        return DataSourceConfigDTO.fromEntity(config);
    }

    private String encryptPassword(String password) {
        // 这里应该实现实际的密码加密逻辑
        // 可以使用BCrypt、AES等加密算法
        // 暂时返回原密码，实际项目中需要实现加密
        return password;
    }
    
    /**
     * 解密密码
     * @param encryptedPassword 加密后的密码
     * @return 解密后的密码
     */
    private String decryptPassword(String encryptedPassword) {
        // 这里应该实现实际的密码解密逻辑
        // 与encryptPassword方法对应
        // 暂时返回原密码，实际项目中需要实现解密
        return encryptedPassword;
    }
    
    /**
     * 根据数据库配置构建JDBC URL
     * @param config 数据库配置
     * @return JDBC URL
     */
    private String buildJdbcUrl(DataSourceConfig config) {
        String dbType = config.getDbType();
        String host = config.getHost();
        Integer port = config.getPort();
        String databaseName = config.getDatabaseName();
        
        switch (dbType.toUpperCase()) {
            case "MYSQL":
                return String.format("jdbc:mysql://%s:%d/%s?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC", 
                    host, port, databaseName);
            case "POSTGRESQL":
                return String.format("jdbc:postgresql://%s:%d/%s", host, port, databaseName);
            case "ORACLE":
                return String.format("jdbc:oracle:thin:@%s:%d:%s", host, port, databaseName);
            case "SQL SERVER":
            case "SQLSERVER":
                return String.format("jdbc:sqlserver://%s:%d;databaseName=%s", host, port, databaseName);
            default:
                //log.error("不支持的数据库类型: {}", dbType);
                return null;
        }
    }

    @Override
    public QueryResultDTO executeQuery(Long id, ExecuteQueryRequest request) {
        DataSourceConfig config = dataSourceConfigMapper.selectById(id);
        if (config == null) {
            throw new RuntimeException("数据源配置不存在，ID: " + id);
        }
        
        return executeQueryInternal(config, request.getSql(), request.getLimit(), request.getTimeoutSeconds());
    }

    @Override
    public QueryResultDTO executeQueryWithParams(ExecuteQueryWithParamsRequest request) {
        // 创建临时的DataSourceConfig对象
        DataSourceConfig tempConfig = new DataSourceConfig();
        tempConfig.setDbType(request.getDbType());
        tempConfig.setHost(request.getHost());
        tempConfig.setPort(request.getPort());
        tempConfig.setUsername(request.getUsername());
        tempConfig.setDatabaseName(request.getDatabaseName());
        
        return executeQueryWithPassword(tempConfig, request.getPassword(), request.getSql(), 
                                      request.getLimit(), request.getTimeoutSeconds());
    }

    @Override
    public QueryResultDTO getTableInfoAndSampleData(Long id, String tableName) {
        DataSourceConfig config = dataSourceConfigMapper.selectById(id);
        if (config == null) {
            throw new RuntimeException("数据源配置不存在，ID: " + id);
        }
        
        String sql = buildTableInfoSql(config.getDbType(), tableName);
        return executeQueryInternal(config, sql, 5, 30);
    }

    @Override
    public QueryResultDTO getTableInfoAndSampleDataWithParams(TestConnectionRequest connectionRequest, String tableName) {
        // 创建临时的DataSourceConfig对象
        DataSourceConfig tempConfig = new DataSourceConfig();
        tempConfig.setDbType(connectionRequest.getDbType());
        tempConfig.setHost(connectionRequest.getHost());
        tempConfig.setPort(connectionRequest.getPort());
        tempConfig.setUsername(connectionRequest.getUsername());
        tempConfig.setDatabaseName(connectionRequest.getDatabaseName());
        
        String sql = buildTableInfoSql(connectionRequest.getDbType(), tableName);
        return executeQueryWithPassword(tempConfig, connectionRequest.getPassword(), sql, 5, 30);
    }

    /**
     * 执行查询的内部方法（使用已保存的配置）
     */
    private QueryResultDTO executeQueryInternal(DataSourceConfig config, String sql, Integer limit, Integer timeoutSeconds) {
        String password = decryptPassword(config.getPasswordEncrypted());
        return executeQueryWithPassword(config, password, sql, limit, timeoutSeconds);
    }

    /**
     * 执行查询的核心方法
     */
    private QueryResultDTO executeQueryWithPassword(DataSourceConfig config, String password, 
                                                   String sql, Integer limit, Integer timeoutSeconds) {
        String jdbcUrl = buildJdbcUrl(config);
        if (jdbcUrl == null) {
            throw new RuntimeException("不支持的数据库类型: " + config.getDbType());
        }
        
        long startTime = System.currentTimeMillis();
        
        try (Connection connection = DriverManager.getConnection(jdbcUrl, config.getUsername(), password)) {
            connection.setReadOnly(true); // 设置为只读模式
            
            // 添加LIMIT子句（如果SQL中没有）
            String finalSql = addLimitToSql(sql, limit, config.getDbType());
            
            try (PreparedStatement statement = connection.prepareStatement(finalSql)) {
                statement.setQueryTimeout(timeoutSeconds);
                
                try (ResultSet resultSet = statement.executeQuery()) {
                    return buildQueryResult(resultSet, finalSql, startTime, limit);
                }
            }
        } catch (SQLException e) {
            //log.error("执行查询失败，SQL: {}, 错误: {}", sql, e.getMessage());
            throw new RuntimeException("查询执行失败: " + e.getMessage(), e);
        }
    }

    /**
     * 构建表信息查询SQL
     */
    private String buildTableInfoSql(String dbType, String tableName) {
        switch (dbType.toUpperCase()) {
            case "MYSQL":
                return String.format("SELECT * FROM `%s` LIMIT 5", tableName);
            case "POSTGRESQL":
                return String.format("SELECT * FROM \"%s\" LIMIT 5", tableName);
            case "ORACLE":
                return String.format("SELECT * FROM \"%s\" WHERE ROWNUM <= 5", tableName);
            case "SQL SERVER":
            case "SQLSERVER":
                return String.format("SELECT TOP 5 * FROM [%s]", tableName);
            default:
                return String.format("SELECT * FROM %s LIMIT 5", tableName);
        }
    }

    /**
     * 为SQL添加LIMIT子句
     */
    private String addLimitToSql(String sql, Integer limit, String dbType) {
        String upperSql = sql.toUpperCase().trim();
        
        // 如果SQL已经包含LIMIT、TOP或ROWNUM，则不添加
        if (upperSql.contains("LIMIT") || upperSql.contains("TOP ") || upperSql.contains("ROWNUM")) {
            return sql;
        }
        
        switch (dbType.toUpperCase()) {
            case "MYSQL":
            case "POSTGRESQL":
                return sql + " LIMIT " + limit;
            case "ORACLE":
                return "SELECT * FROM (" + sql + ") WHERE ROWNUM <= " + limit;
            case "SQL SERVER":
            case "SQLSERVER":
                // 对于SQL Server，需要在SELECT后添加TOP
                if (upperSql.startsWith("SELECT")) {
                    return sql.replaceFirst("(?i)SELECT", "SELECT TOP " + limit);
                }
                return sql;
            default:
                return sql + " LIMIT " + limit;
        }
    }

    /**
     * 构建查询结果对象
     */
    private QueryResultDTO buildQueryResult(ResultSet resultSet, String executedSql, long startTime, Integer limit) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        
        // 获取列名
        List<String> columnNames = new ArrayList<>();
        for (int i = 1; i <= columnCount; i++) {
            columnNames.add(metaData.getColumnName(i));
        }
        
        // 获取数据行
        List<Map<String, Object>> rows = new ArrayList<>();
        int rowCount = 0;
        while (resultSet.next() && rowCount < limit) {
            Map<String, Object> row = new LinkedHashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object value = resultSet.getObject(i);
                row.put(columnName, value);
            }
            rows.add(row);
            rowCount++;
        }
        
        // 检查是否还有更多数据
        boolean hasMore = resultSet.next();
        
        long executionTime = System.currentTimeMillis() - startTime;
        
        return new QueryResultDTO(columnNames, rows, rowCount, hasMore, executedSql, executionTime);
    }
}